#!/usr/bin/python3
# -*- coding: utf-8 -*-

from sqlalchemy import (
    MetaData, Table, Column, ForeignKey,
    Integer, Float, String, DateTime, Boolean, Text, func
)
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

import sys
import settings

Base = declarative_base()
DSN = "sqlite:///{db_file}?check_same_thread=False"


def create_tables():
    global engine
    Base.metadata.create_all(engine)


def create_session():
    global engine
    # engine是2.2中创建的连接
    Session = sessionmaker(bind=engine)
    # 创建Session类实例
    session = Session()
    return session


def init_db(env='pro'):
    global engine
    settings.get_config(env)
    conf = settings.config['sqlite']
    db_url = DSN.format(**conf)
    # engine = create_engine(db_url, echo=True)
    engine = create_engine(db_url, echo=settings.config['sqlite']['echo'])


class Hlsitem(Base):
    __tablename__ = 'Hlsitem'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    name = Column(String(100))
    # -1: 暂停, 0: 下载中, 1: 下载完成
    status = Column(Integer, default=-1)
    size = Column(Integer, default=0)
    path = Column(String(512), default='/root')
    url = Column(String(512))
    poster = Column(String(512))
    referer = Column(String(512))
    referer_referer = Column(String(512))
    poster_referer = Column(String(512))
    content_type = Column(String(20))
    download_count = Column(Integer)
    duration = Column(Integer, default=0)
    show = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.now)
    created_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    created_stamp = Column(Float, default=lambda: datetime.now().timestamp())
    updated_at = Column(DateTime, default=datetime.now)
    updated_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    updated_stamp = Column(Float, default=lambda: datetime.now().timestamp())


    def __repr__(self):
        return "<%s%s>" % (self.__tablename__, str({c.name: getattr(self, c.name) for c in self.__table__.columns}))
    # 单个对象方法2

    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                val = getattr(self, key)
                if isinstance(val, datetime):
                    result[key] = val.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    result[key] = str(val)
            else:
                result[key] = getattr(self, key)
        return result


class Proxyitem(Base):
    __tablename__ = 'Proxyitem'

    id = Column(Integer, primary_key=True)
    ip = Column(String(100))
    port = Column(String(6))
    # 匿名度
    anony = Column(String(32))
    http_type = Column(String(10))
    location = Column(String(100))
    ping = Column(Float)
    validate_time = Column(String(512))
    referer = Column(String(512))
    created_at = Column(DateTime, default=datetime.now)
    created_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    created_stamp = Column(Float, default=lambda: datetime.now().timestamp())
    updated_at = Column(DateTime, default=datetime.now)
    updated_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    updated_stamp = Column(Float, default=lambda: datetime.now().timestamp())


    def __repr__(self):
        return "<%s%s>" % (self.__tablename__, str({c.name: getattr(self, c.name) for c in self.__table__.columns}))
    # 单个对象方法2

    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                val = getattr(self, key)
                if isinstance(val, datetime):
                    result[key] = val.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    result[key] = str(val)
            else:
                result[key] = getattr(self, key)
        return result


class Datars(Base):
    __tablename__ = 'Datars'
    id = Column(Integer, primary_key=True)
    method = Column(String(6))
    path = Column(String(128))
    handler = Column(String(64))
    module = Column(String(16))
    rcv = Column(String(512))
    snd = Column(String(512))
    content_type = Column(String(32))
    response_type = Column(String(32))
    created_at = Column(DateTime, default=datetime.now)
    created_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    created_stamp = Column(Float, default=lambda: datetime.now().timestamp())
    updated_at = Column(DateTime, default=datetime.now)
    updated_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    updated_stamp = Column(Float, default=lambda: datetime.now().timestamp())

    def __repr__(self):
        return "<%s%s>" % (self.__tablename__, str({c.name: getattr(self, c.name) for c in self.__table__.columns}))
    # 单个对象方法2

    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                val = getattr(self, key)
                if isinstance(val, datetime):
                    result[key] = val.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    result[key] = str(val)
            else:
                result[key] = getattr(self, key)
        return result


class Blog(Base):
    __tablename__ = 'Blog'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    name = Column(String(256))
    path = Column(String(512), default='/root')
    summary = Column(String(512))
    content = Column(Text)
    # 'd'-文件夹, '-'-普通文件
    content_type = Column(String(16))
    # 2: 审核中, 3: 公开
    status = Column(Integer, default=2)
    created_at = Column(DateTime, default=datetime.now)
    created_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    created_stamp = Column(Float, default=lambda: datetime.now().timestamp())
    updated_at = Column(DateTime, default=datetime.now)
    updated_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    updated_stamp = Column(Float, default=lambda: datetime.now().timestamp())

    def __repr__(self):
        return "<%s%s>" % (self.__tablename__, str({c.name: getattr(self, c.name) for c in self.__table__.columns}))

    # 单个对象方法2
    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                val = getattr(self, key)
                if isinstance(val, datetime):
                    result[key] = val.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    result[key] = str(val)
            else:
                result[key] = getattr(self, key)
        return result


class Script(Base):
    __tablename__ = 'Script'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)
    name = Column(String(256))
    path = Column(String(512), default='/root')
    content = Column(Text)
    # 'd'-文件夹, '-'-普通文件
    content_type = Column(String(16))
    # 2: 审核中, 3: 公开
    status = Column(Integer, default=2)
    created_at = Column(DateTime, default=datetime.now)
    created_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    created_stamp = Column(Float, default=lambda: datetime.now().timestamp())
    updated_at = Column(DateTime, default=datetime.now)
    updated_date = Column(
        String(8), default=lambda: datetime.now().strftime('%Y%m%d'))
    updated_stamp = Column(Float, default=lambda: datetime.now().timestamp())

    def __repr__(self):
        return "<%s%s>" % (self.__tablename__, str({c.name: getattr(self, c.name) for c in self.__table__.columns}))
    # 单个对象方法2

    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                val = getattr(self, key)
                if isinstance(val, datetime):
                    result[key] = val.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    result[key] = str(val)
            else:
                result[key] = getattr(self, key)
        return result


def sample_data(session):
    data = []
    if len([item for item in session.query(Hlsitem).filter_by(name='root', path='/')]) <= 0:
        data.append(Hlsitem(name='root', path='/', content_type='d'))
    if len([item for item in session.query(Blog).filter_by(name='root', path='/')]) <= 0:
        data.append(Blog(name='root', path='/', content_type='d'))
    if len([item for item in session.query(Script).filter_by(name='root', path='/')]) <= 0:
        data.append(Script(name='root', path='/', content_type='d'))
    session.add_all(data)
    session.commit()


def execute_sql(sql):
    results = []
    try:
        session = create_session()
        sqls = sql.split(';')
        cud = []
        retrieve = []
        for item in sqls:
            item = item.strip()
            if len(item) <= 0:
                continue
            if item.upper().startswith('SELECT'):
                retrieve.append(item)
            else:
                cud.append(item)
        for item in sqls:
            item = item.strip()
            if len(item) <= 0:
                continue
            ret = session.execute('%s;' % item)
            if item in retrieve:
                ret = [dict((zip(item.keys(), item)))
                       for item in ret.fetchall()]
                results.append(ret)
            else:
                results.append('%s: SUCCESS' % item)
        if len(cud) > 0:
            session.commit()
    except BaseException as e:
        return {'status': False, 'msg': e}
    return {'status': True, 'msg': results}


if __name__ == '__main__':
    env = 'pro'
    if len(sys.argv) == 2:
        env = sys.argv[1]
    init_db(env)
    create_tables()
    session = create_session()
    sample_data(session)
    # hlsitem = [item for item in session.query(Hlsitem)]
    print('data compelte')
